A Brief Discussion on NULL Comparisons in SQL Server WHERE Clauses
Recently at work, I was surprised to hear that a colleague didn't know that when checking for NULL in a SQL WHERE clause, one must use IS NULL instead of = NULL. To me, this is basic knowledge; in my understanding, whether learning from a database instructor or self-studying from a SQL book, this is something you should know as soon as you learn about WHERE clauses. I can only say that, in this regard, ORMs have spoiled some people.
What surprised me even more was that a colleague with more seniority than me was also unaware of this. However, thinking about it carefully, perhaps I misheard during the meeting and misunderstood the context—maybe they were just checking someone else's error rather than being unaware themselves.
Therefore, I brought my junior colleague over to brush up on some fundamentals, and wrote a note for her to read. In reality, I wanted to confirm the details myself.
The Result of NULL Comparisons
First, many people mistakenly believe that the result of a SQL logical comparison is only TRUE or FALSE, but in reality, there is also UNKNOWN. Although I knew when I first learned SQL that NULL checks required IS NULL or IS NOT NULL, I only learned the concept of UNKNOWN a few months ago.
Since NULL represents an unknown value, any comparison between any value (including NULL) and NULL results in UNKNOWN, except when using IS NULL or IS NOT NULL. In a WHERE clause, only data that evaluates to TRUE is included in the query results.
Logical Operations with UNKNOWN
The following table lists the results of Expression 1 AND Expression 2 when one of the expressions is UNKNOWN.
| Expression 1 | Expression 2 | Result |
|---|---|---|
| TRUE | UNKNOWN | UNKNOWN |
| UNKNOWN | UNKNOWN | UNKNOWN |
| FALSE | UNKNOWN | FALSE |
The following table lists the results of Expression 1 OR Expression 2 when one of the expressions is UNKNOWN.
| Expression 1 | Expression 2 | Result |
|---|---|---|
| TRUE | UNKNOWN | TRUE |
| UNKNOWN | UNKNOWN | UNKNOWN |
| FALSE | UNKNOWN | UNKNOWN |
Honestly, it's a bit hard to remember, so it is recommended to avoid involving UNKNOWN in your logic as much as possible.
SQL Standards and Not-Equal Operators
This section is unrelated to the main topic but is recorded here for reference.
SQL Standards
Common SQL standards include:
- ANSI SQL: The SQL standard established by the American National Standards Institute (ANSI).
- T-SQL: Microsoft SQL Server's implementation of the ANSI SQL standard, with additional features and extensions.
- PL/SQL: Oracle's implementation of the ANSI SQL standard, with additional features and extensions.
Not-Equal Operators
In early ANSI SQL standards, <> was the only explicitly defined not-equal operator. Starting with SQL-92, some database systems began supporting != as an optional not-equal operator. It is speculated that this is because other programming languages use != as the not-equal operator, but I am not certain if it was subsequently included in the SQL standard. There are still a very small number of databases, such as Microsoft Access, that do not support !=.
Although SQL Server supports !=, the official documentation still uses <> as the standard not-equal operator.
References
Change Log
- 2024-07-24 Initial version created.